﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Login
{
    public partial class FormStuInfo : FormBase
    {
        public FormStuInfo(ToolStripMenuItem tsmi) : base(tsmi)
        {
            InitializeComponent();
        }

        private void FormStuInfo_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=STUDB;Integrated Security=True");
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from student";

            conn.StateChange += Conn_StateChange;

            SqlDataAdapter adaper = new SqlDataAdapter();
            adaper.SelectCommand = cmd;

            DataTable dt = new DataTable();
            adaper.Fill(dt);

            DataView dv = new DataView(dt, "sex = '男'", "age desc", DataViewRowState.CurrentRows);
            BindingSource bs = new BindingSource();
            bs.DataSource = dv;
            dataGV.DataSource = bs;
            //bindingNvg.BindingSource = bs;

            dv = new DataView(dt, "sex = '女'", "age", DataViewRowState.CurrentRows);
            bs = new BindingSource();
            bs.DataSource = dv;
            dataGV1.DataSource = bs;
        }

        private void Conn_StateChange(object sender, StateChangeEventArgs e)
        {
        }

        protected override void Delete()
        {
            if (dataGV.DataSource is BindingSource)
            {
                BindingSource bs = dataGV.DataSource as BindingSource;
                bs.RemoveCurrent();
            }
        }

        private void tsbCopy_Click(object sender, EventArgs e)
        {
            if (dataGV.DataSource is BindingSource)
            {
                BindingSource bs = dataGV.DataSource as BindingSource;
                DataRowView drv = (DataRowView)bs.Current;

                DataRow dr = ((DataView)bs.DataSource).Table.NewRow();
                ((DataView)bs.DataSource).Table.Rows.Add(dr);
                dr.ItemArray = drv.Row.ItemArray;
            }
        }

        private void tsbSave_Click(object sender, EventArgs e)
        {
            if (dataGV.DataSource is BindingSource)
            {
                BindingSource bs = dataGV.DataSource as BindingSource;

                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=STUDB;Integrated Security=True");
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    DataTable dataChanged = ((DataView)bs.DataSource).Table.GetChanges(DataRowState.Deleted);
                    if (dataChanged != null)
                    {
                        //模仿修改代码，完成数据删除
                    }
                    dataChanged = ((DataView)bs.DataSource).Table.GetChanges(DataRowState.Modified);
                    if (dataChanged != null)
                    {
                        string sql = @"update student set sno = @sno, sname = @sname, sex = @sex, dept = @dept, age = @age
                        where sno = @oldsno and sname = @oldsname and sex = @oldsex and dept = @olddept and Age = @oldage";
                        cmd.CommandText = sql;

                        foreach (DataRow dr in dataChanged.Rows)
                        {
                            cmd.Parameters.Clear();
                            SqlParameter param = new SqlParameter();
                            param.ParameterName = "sno";
                            param.Value = dr["sno", DataRowVersion.Current];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "sname";
                            param.Value = dr["sname", DataRowVersion.Current];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "sex";
                            param.Value = dr["sex", DataRowVersion.Current];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "dept";
                            param.Value = dr["dept", DataRowVersion.Current];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "age";
                            param.Value = dr["age", DataRowVersion.Current];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "oldsname";
                            param.Value = dr["sname", DataRowVersion.Original];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "oldsno";
                            param.Value = dr["sno", DataRowVersion.Original];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "oldsex";
                            param.Value = dr["sex", DataRowVersion.Original];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "olddept";
                            param.Value = dr["dept", DataRowVersion.Original];
                            cmd.Parameters.Add(param);

                            param = new SqlParameter();
                            param.ParameterName = "oldage";
                            param.Value = dr["age", DataRowVersion.Original];
                            cmd.Parameters.Add(param);
                            int rowAffected = cmd.ExecuteNonQuery();
                        }
                    }
                    dataChanged = ((DataView)bs.DataSource).Table.GetChanges(DataRowState.Added);
                    if (dataChanged != null)
                    {

                        //模仿修改代码，完成数据插入操作                    
                    }                    
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    if (conn != null && conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
}
